﻿using System;
using System.Data;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;

namespace EssHelper.Helper
{
    /// <summary>
    /// Excel数据处理
    /// </summary>
    public class ExcelManager
    {
        /// <summary>
        /// 读取数据
        /// </summary>
        /// <remarks>
        /// 实现流程：
        ///     读取数据
        /// </remarks>
        /// <param name="fileName"></param>
        /// <param name="fill"></param>
        /// <returns></returns>
        public static DataSet ReadFromExcelFile(string fileName)
        {
            // 判断条件 
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException("fileName");
            }

            HSSFWorkbook hssfworkbook = null;
            // 使用实现IDisposable接口类，自动释放资源
            using (FileStream fs = File.Open(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.None))
            {
                hssfworkbook = new HSSFWorkbook(fs);
            }
            System.Diagnostics.Debug.Assert(null != hssfworkbook, "打开excel 失败！");

            DataSet dsData = new DataSet();
            // 递增 循环处理 
            for (int sheetIndex = 0; sheetIndex < hssfworkbook.NumberOfSheets; sheetIndex++)
            {
                DataTable dtData = new DataTable(hssfworkbook.GetSheetName(sheetIndex));

                Sheet sheet = hssfworkbook.GetSheetAt(sheetIndex);
                for (int rowIndex = sheet.FirstRowNum; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++)
                {
                    Row row = sheet.GetRow(rowIndex);

                    int lastRowIndex = row.LastCellNum;
                    object[] objArray = new object[lastRowIndex];
                    for (int colIndex = row.FirstCellNum; colIndex < lastRowIndex; colIndex++)
                    {
                        objArray.SetValue(GetCellValue(row.GetCell(colIndex)), colIndex);
                    }
                    dtData.LoadDataRow(objArray, true);
                }
                dtData.AcceptChanges();
                dsData.Tables.Add(dtData);
            }
            dsData.AcceptChanges();

            return dsData;
        }

        /// <summary>
        /// 写入到Excel数据中
        /// </summary>
        /// <remarks>
        /// 实现流程：
        ///     写入到Excel数据中
        /// </remarks>
        /// <param name="fileName"></param>
        /// <param name="fill"></param>
        /// <returns></returns>
        public static void WriteToExcelFile(string fileName, DataSet dsData)
        {
            // 判断条件 
            if (string.IsNullOrEmpty(fileName))
            {
                throw new ArgumentNullException("fileName");
            }

            HSSFWorkbook hssfworkbook = null;
            // 使用实现IDisposable接口类，自动释放资源
            using (FileStream fs = File.Open(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.None))
            {
                hssfworkbook = new HSSFWorkbook(fs);
            }
            System.Diagnostics.Debug.Assert(null != hssfworkbook, "打开excel 失败！");

            //循环数据表处理
            for (int tableIndex = 0; tableIndex < dsData.Tables.Count; tableIndex++)
            {
                DataTable dtData = dsData.Tables[tableIndex];

                //获取现有的模板Sheet
                Sheet sheet = hssfworkbook.GetSheetAt(tableIndex);
                Row headRow = sheet.CreateRow(0);

                //构建列头
                int i = 0;
                foreach (DataColumn column in dtData.Columns) 
                {
                    headRow.CreateCell(i).SetCellValue(column.ColumnName);
                    i++;
                }

                //填充行
                for (int rowIndex = 0; rowIndex < dtData.Rows.Count; rowIndex++)
                {
                    DataRow dr = dtData.Rows[rowIndex];
                    Row row = sheet.CreateRow(rowIndex + 1);

                    for (int colIndex = 0; colIndex < dtData.Columns.Count; colIndex++)
                    {
                        row.CreateCell(colIndex).SetCellValue(dr[colIndex].ToString());
                    }
                }
            }

            // 使用实现IDisposable接口类，自动释放资源
            using (FileStream fs = File.Open(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
            {
                hssfworkbook.Write(fs);
            }
        }

        /// <summary>
        /// 读取单元格的数值
        /// </summary>
        /// <remarks>
        /// 实现流程：
        ///     读取单元格的数值
        /// </remarks>
        /// <param name="cell"></param>
        /// <returns></returns>
        private static object GetCellValue(Cell cell)
        {
            if (null != cell)
            {
                switch (cell.CellType)
                {
                    case CellType.BOOLEAN:
                        return cell.BooleanCellValue;

                    case CellType.ERROR:
                        return cell.ErrorCellValue;

                    case CellType.FORMULA:
                        return cell.CellFormula;

                    case CellType.NUMERIC:
                        return cell.NumericCellValue;

                    case CellType.STRING:
                        return cell.StringCellValue;

                    case CellType.BLANK:
                    case CellType.Unknown:
                    default:
                        return string.Empty;
                }
            }
            return string.Empty;
        }
    }
}
